SQL INSERT Examples 1-6

--Example 1. Add a new faculty record.
INSERT
INTO	Faculty (facId, name, department, rank)
VALUES	('F330', 'Jones', 'CSC', 'Instructor');

--Example 2. Add a new student record, without specifying fields
INSERT
INTO	Student
VALUES	('S1030','Hunt', 'Alice', 'Art', 12);

-Example 3. Add a new student record with fields rearranged, and a null value for major.
INSERT
INTO	Student ( lastName, firstName, StuId, credits)
VALUES	(' Bono','Maria', 'S1031', 0);


--Example 4. Create a new table that holds each class number and the number of students in that class.

CREATE TABLE Enrollment
		(classNumber  CHAR(8) NOT NULL,
		Students SMALLINT);

	INSERT
	INTO Enrollment (classNumber, Students)
		SELECT	classNumber, COUNT(*)
		FROM	Enroll
		GROUP BY  classNumber;

--Example 5. Inserting DATE values and SYSDATE
--5(a) creating an Employee table

create table Employee(
empId		number(5), 
lastName	varchar2(20), 
firstName	varchar2(20), 
birthDate	date, 
hireDate	date,
constraint Emp_empid_pk primary key (empid));

--5(b) inserting a record using the default format for dates

INSERT
INTO EMPLOYEE
VALUES(1001, 'Hynes','Susan','15-OCT-1985','01-JUN-2010');


--(c) using SYSDATE to insert today's date, 


DELETE FROM EMPLOYEE WHERE EMPID =1001;

INSERT
INTO EMPLOYEE
VALUES(1001, 'Hynes','Susan','15-OCT-1985',SYSDATE);

--(d) retrieving only the year part of a date

SELECT firstName, lastName, TO_CHAR(birthDate, 'YYYY') AS YEAR
FROM Employee
WHERE empId = 1001;

 --(e)  converting from a string to a date format

INSERT
INTO Employee (empId, firstName, lastName, hireDate)
VALUES(1002, 'Scott','Kane',TO_DATE('01/01/2012','MM/DD/YYYY'));


-- Example 6. Creating and Using Sequences

--(a) creating a sequence
CREATE SEQUENCE empId_seq START WITH 1001 INCREMENT BY 1;


--(b) inserting records using a sequence

--first we delete the records stored earlier
DELETE FROM Employee;

INSERT
INTO Employee
VALUES (empId_seq.NEXTVAL, 'Hynes','Susan','15-OCT-1981',SYSDATE);

INSERT
INTO Employee(empId, firstName, lastName, hireDate)
VALUES (empId_seq.NEXTVAL,
'Scott','Kane',TO_DATE('01/01/2012','MM/DD/YYYY'));

--(c) inserting a record with a value to match a sequence field in another table
 
create table Assignment(
empId	number(5), 
projNo	varchar2(4), 
hoursAssigned	number(3),
constraint Assign_empid_projNo_pk primary key (empid, projNo),
constraint Assign_empid_fk foreign key(empId) references Employee(empId));


INSERT
INTO Assignment
VALUES (empId_seq.CURRVAL, 'J10', 30);

--(d) retrieving the current value of a sequence

SELECT empId_seq.CURRVAL from DUAL;

--(e) dropping a sequence
DROP SEQUENCE empId_seq;

